﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

namespace Daneas.Utility.Data
{
    public class MySqlHelper
    {
        public static string mysqlSource = ConfigurationManager.ConnectionStrings["mysqlSource"].ToString();

        public static int timeOut = PubConstant.TimeOut <= 0 ? 20 : PubConstant.TimeOut;

        #region 公用方法

        /// <summary>
        /// 判断是否存在某表的某个字段
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="columnName">列名称</param>
        /// <returns>是否存在</returns>
        public static bool ColumnExists(string tableName, string columnName)
        {
            string sql = "select count(1) from syscolumns where [id]=object_id('" + tableName + "') and [name]='" + columnName + "'";

            object res = ExecuteScalar(sql);

            if (res == null || res == DBNull.Value)
            {
                return false;
            }
            return Convert.ToInt32(res) > 0;
        }

        /// <summary>
        /// 获取字段的最大值
        /// </summary>
        /// <param name="FieldName"></param>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public static int GetMaxID(string FieldName, string TableName)
        {
            string strsql = "select max(" + FieldName + ") as MaxValue from " + TableName;

            object obj = ExecuteScalar(strsql);

            if (obj == null || obj == DBNull.Value)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 表是否存在
        /// </summary>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public static bool TabExists(string TableName)
        {
            string strsql = "select count(*) from sysobjects where id = object_id(N'[" + TableName + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1";

            //string strsql = "SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + TableName + "]') AND type in (N'U')";
            object obj = ExecuteScalar(strsql);

            if (obj == null || obj == DBNull.Value)
            {
                return false;
            }

            return Convert.ToInt32(obj) > 0;
        }

        /// <summary>
        /// 是否存在对象
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public static bool Exists(string strSql)
        {
            object obj = ExecuteNonQuery(strSql);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString()); //也可能=0
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        /// <summary>
        /// 带参查询是否存在对象
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public static bool Exists(string strSql, params MySqlParameter[] cmdParms)
        {
            object obj = ExecuteNonQuery(strSql, cmdParms);

            int cmdresult;

            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        public static int GetCount(string sql)
        {
            StringBuilder sb_count = new StringBuilder();

            sb_count.Append("SELECT COUNT(*) FROM (");
            sb_count.Append(sql);
            sb_count.Append(" ) T");

            object obj = ExecuteScalar(sb_count.ToString());

            if (obj == DBNull.Value || obj == null)
            {
                return 0;
            }

            return Convert.ToInt32(obj);
        }

        public static int GetCount(string sql, MySqlParameter param)
        {
            StringBuilder sb_count = new StringBuilder();

            sb_count.Append("SELECT COUNT(*) FROM (");
            sb_count.Append(sql);
            sb_count.Append(" ) T");

            object obj = ExecuteScalar(sb_count.ToString(), param);

            if (obj == DBNull.Value || obj == null)
            {
                return 0;
            }

            return Convert.ToInt32(obj);
        }

        public static int GetCount(string sql, MySqlParameter[] paramList)
        {
            StringBuilder sb_count = new StringBuilder();

            sb_count.Append("SELECT COUNT(*) FROM (");
            sb_count.Append(sql);
            sb_count.Append(" ) T");

            object obj = ExecuteScalar(sb_count.ToString(), paramList);

            if (obj == DBNull.Value || obj == null)
            {
                return 0;
            }

            return Convert.ToInt32(obj);
        }

        #endregion

        /// <summary>
        /// 处理连接
        /// <remarks>cmd为空的时候要坚持链接状态</remarks>
        /// </summary>
        public static void MySqlPrepare(MySqlConnection conn, MySqlCommand cmd, string sql, MySqlParameter[] paramList)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Close();
            }

            conn.Open();

            if (cmd != null)
            {
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;
                cmd.CommandTimeout = timeOut;
                cmd.Parameters.Clear();
            }

            if (paramList != null)
            {
                foreach (MySqlParameter param in paramList)
                {
                    if (param.Value == null)
                    {
                        param.Value = DBNull.Value;
                    }

                    cmd.Parameters.Add(param);
                }
            }
        }

        public static MySqlCommand TranCmdPrepare(MySqlCommand cmd, string sql, MySqlParameter[] paramList)
        {
            if (cmd != null)
            {
                cmd.Parameters.Clear();

                cmd.CommandText = sql;

                cmd.CommandType = CommandType.Text;

                if (paramList != null && paramList.Length > 0)
                {
                    foreach (MySqlParameter param in paramList)
                    {
                        cmd.Parameters.Add(param);
                    }
                }

                return cmd;
            }

            return new MySqlCommand();
        }

        public static MySqlConnection GetConn(MySqlConnection conn)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Close();
                conn.Open();
                return conn;
            }

            return conn;
        }

        #region 增删改

        public static int ExecuteNonQuery(string sql)
        {
            using (MySqlConnection conn = new MySqlConnection(mysqlSource))
            {
                MySqlCommand cmd = new MySqlCommand();

                MySqlPrepare(conn, cmd, sql, null);

                try
                {
                    int var = cmd.ExecuteNonQuery();

                    return var;
                }
                catch 
                {
                    throw new Exception();
                }
                finally
                {
                    cmd.Parameters.Clear();
                    cmd.Dispose();
                }
            }
        }

        public static int ExecuteNonQuery(string sql, MySqlCommand cmd)
        {
            try
            {
                cmd = TranCmdPrepare(cmd, sql, null);

                int var = cmd.ExecuteNonQuery();

                return var;
            }
            catch 
            {
                throw new Exception();
            }
        }

        public static int ExecuteNonQuery(string sql, MySqlParameter[] paramList)
        {
            using (MySqlConnection conn = new MySqlConnection(mysqlSource))
            {
                MySqlCommand cmd = new MySqlCommand();

                MySqlPrepare(conn, cmd, sql, paramList);

                try
                {
                    int var = cmd.ExecuteNonQuery();

                    return var;
                }
                catch
                {
                    throw new Exception();
                }
                finally
                {
                    cmd.Parameters.Clear();
                    cmd.Dispose();
                }
            }
        }

        public static int ExecuteNonQuery(string sql, MySqlParameter param)
        {
            MySqlParameter[] paramList = { param };

            return ExecuteNonQuery(sql, paramList);
        }

        public static int ExecuteNonQuery(string sql, MySqlParameter[] paramList, MySqlCommand cmd)
        {
            cmd = TranCmdPrepare(cmd, sql, paramList);

            int var = cmd.ExecuteNonQuery();

            cmd.Parameters.Clear();

            return var;
        }

        public static int ExecuteNonQuery(string sql, MySqlParameter param, MySqlCommand cmd)
        {
            MySqlParameter[] paramList = { param };

            return ExecuteNonQuery(sql, paramList, cmd);
        }

        #endregion

        #region 查一个object

        public static object ExecuteScalar(string sql)
        {
            using (MySqlConnection conn = new MySqlConnection(mysqlSource))
            {
                MySqlCommand cmd = new MySqlCommand();

                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }

                conn.Open();

                cmd.Connection = conn;

                cmd.CommandText = sql;

                try
                {
                    object val = cmd.ExecuteScalar();

                    return val;
                }
                catch 
                {
                    throw new Exception();
                }
            }
        }

        public static object ExecuteScalar(string sql, MySqlCommand cmd)
        {
            cmd = TranCmdPrepare(cmd, sql, null);

            object val = cmd.ExecuteScalar();

            cmd.Parameters.Clear();

            return val;
        }

        public static object ExecuteScalar(string sql, MySqlParameter[] paras)
        {
            using (MySqlConnection conn = new MySqlConnection(mysqlSource))
            {
                MySqlCommand cmd = new MySqlCommand();

                MySqlPrepare(conn, cmd, sql, paras);

                try
                {
                    object val = cmd.ExecuteScalar();

                    return val;
                }
                catch 
                {
                    throw new Exception();
                }
                finally
                {
                    cmd.Parameters.Clear();
                }
            }
        }

        public static object ExecuteScalar(string sql, MySqlParameter param)
        {
            MySqlParameter[] paramList = { param };

            return ExecuteScalar(sql, paramList);
        }

        public static object ExecuteScalar(string sql, MySqlParameter[] paras, MySqlCommand cmd)
        {
            cmd = TranCmdPrepare(cmd, sql, paras);

            object val = cmd.ExecuteScalar();

            cmd.Parameters.Clear();

            return val;
        }

        public static object ExecuteScalar(string sql, MySqlParameter param, MySqlCommand cmd)
        {
            MySqlParameter[] paramList = { param };

            return ExecuteScalar(sql, paramList, cmd);
        }

        #endregion

        #region 查DataSet

        public static DataSet ExecutQuery(string sql)
        {
            using (MySqlConnection conn = new MySqlConnection(mysqlSource))
            {
                DataSet ds = new DataSet();

                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }

                conn.Open();

                MySqlDataAdapter adapter = new MySqlDataAdapter(sql, conn);

                try
                {
                    adapter.Fill(ds, "ds");
                }
                catch (MySqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    adapter.Dispose();
                }

                return ds;
            }
        }

        public static DataSet ExecutQuery(string sql, MySqlCommand cmd)
        {
            DataSet ds = new DataSet();
            try
            {
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);

                adapter.SelectCommand.Parameters.Clear();
                adapter.SelectCommand.CommandText = sql;

                adapter.Fill(ds, "ds");

                adapter.SelectCommand.Parameters.Clear();
                adapter.Dispose();

                cmd.Parameters.Clear();

                return ds;
            }
            catch
            {
                throw new Exception();
            }
        }

        public static DataSet ExecutQuery(string sql, MySqlParameter param)
        {
            MySqlParameter[] paramList = { param };

            return ExecutQuery(sql, paramList);
        }

        public static DataSet ExecutQuery(string sql, MySqlParameter[] paramList)
        {
            using (MySqlConnection conn = new MySqlConnection(mysqlSource))
            {
                DataSet ds = new DataSet();

                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }

                conn.Open();

                MySqlDataAdapter adapter = new MySqlDataAdapter(sql, conn);

                try
                {
                    if (paramList != null && paramList.Length > 0)
                    {
                        foreach (MySqlParameter param in paramList)
                        {
                            adapter.SelectCommand.Parameters.Add(param);
                        }
                    }

                    adapter.Fill(ds, "ds");

                    return ds;
                }
                catch
                {
                    throw new Exception();
                }
                finally
                {
                    adapter.Dispose();
                }
            }
        }

        public static DataSet ExecutQuery(string sql, MySqlParameter param, MySqlCommand cmd)
        {
            MySqlParameter[] paramList = { param };

            return ExecutQuery(sql, paramList, cmd);
        }

        public static DataSet ExecutQuery(string sql, MySqlParameter[] paramList, MySqlCommand cmd)
        {
            DataSet ds = new DataSet();
            try
            {
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);

                adapter.SelectCommand.CommandText = sql;

                if (paramList != null && paramList.Length > 0)
                {
                    foreach (MySqlParameter param in paramList)
                    {
                        adapter.SelectCommand.Parameters.Add(param);
                    }
                }


                adapter.Fill(ds, "ds");

                adapter.SelectCommand.Parameters.Clear();
                adapter.Dispose();

                cmd.Parameters.Clear();

                return ds;
            }
            catch
            {
                throw new Exception();
            }
        }

        #endregion

        #region 查SqlDataReader

        public static MySqlDataReader ExecuteGetReader(string sql)
        {
            using (MySqlConnection conn = new MySqlConnection(mysqlSource))
            {
                MySqlCommand cmd = new MySqlCommand();

                MySqlPrepare(conn, cmd, sql, null);

                cmd.Connection = conn;

                try
                {
                    MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                    return reader;  //注意不能关闭连接，否则调用方无法读取数据。
                }
                catch 
                {
                    throw new Exception();
                }
            }
        }

        public static MySqlDataReader ExecuteGetReader(string sql, MySqlCommand cmd)
        {
            using (MySqlConnection conn = new MySqlConnection(mysqlSource))
            {
                cmd = TranCmdPrepare(cmd, sql, null);

                MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                cmd.Parameters.Clear();

                return reader;  //注意不能关闭连接，否则调用方无法读取数据。
            }
        }

        public static MySqlDataReader ExecuteGetReader(string sql, MySqlParameter[] paras)
        {
            using (MySqlConnection conn = new MySqlConnection(mysqlSource))
            {
                MySqlCommand cmd = new MySqlCommand();

                MySqlPrepare(conn, cmd, sql, paras);

                try
                {
                    MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                    return reader;  //注意不能关闭连接，否则调用方无法读取数据。
                }
                catch 
                {
                    throw new Exception();
                }
            }
        }

        public static MySqlDataReader ExecuteGetReader(string sql, MySqlParameter param)
        {
            MySqlParameter[] paramList = { param };

            return ExecuteGetReader(sql, paramList);
        }

        public static MySqlDataReader ExecuteGetReader(string sql, MySqlParameter[] paras, MySqlCommand cmd)
        {
            using (MySqlConnection conn = new MySqlConnection(mysqlSource))
            {
                cmd = TranCmdPrepare(cmd, sql, paras);

                MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                cmd.Parameters.Clear();

                return reader;  //注意不能关闭连接，否则调用方无法读取数据。
            }
        }

        public static MySqlDataReader ExecuteGetReader(string sql, MySqlParameter param, MySqlCommand cmd)
        {
            MySqlParameter[] paramList = { param };

            return ExecuteGetReader(sql, paramList, cmd);
        }

        #endregion

        #region 分页查询

        public static DataTable QueryPage(string sql,int pageIndex, int pageSize, out int totalCount)
        {
            List<MySqlParameter> param = new List<MySqlParameter>();

            return QueryPage(sql, param.ToArray(), pageIndex, pageSize, out totalCount);
        }

        public static DataTable QueryPage(string sql, MySqlParameter param, int pageIndex, int pageSize, out int totalCount)
        {
            List<MySqlParameter> paramList = new List<MySqlParameter>();
            paramList.Add(param);

            return QueryPage(sql, paramList.ToArray(), pageIndex, pageSize, out totalCount);
        }

        public static DataTable QueryPage(string sql, MySqlParameter[] paramList, int pageIndex, int pageSize, out int totalCount)
        {
            if (pageIndex <= 0 || pageSize <= 0)
            {
                pageIndex = 1;
                pageSize = 10;
            }

            int start = pageSize * (pageIndex - 1);

            string exSql = sql + " limit " + start + "," + pageSize;
            DataTable dt = ExecutQuery(exSql, paramList).Tables[0];

            if (dt == null || dt.Rows.Count <= 0)
            {
                totalCount = 0;
                return new DataTable();
            }

            totalCount = GetCount(sql, DeepCloneMySqlParameters(paramList));

            return dt;
        }

        #endregion

        /// <summary>
        /// 执行事务sql
        /// </summary>
        /// <param name="SQLStringList"></param>
        /// <returns></returns>
        public static int ExecuteSqlTran(List<String> SQLStringList)
        {
            using (MySqlConnection conn = new MySqlConnection(mysqlSource))
            {
                MySqlCommand cmd = new MySqlCommand();

                if (conn.State != ConnectionState.Open)
                {
                    conn.Close();
                    conn.Open();
                }

                cmd.Connection = conn;

                MySqlTransaction tran = conn.BeginTransaction();
                cmd.Transaction = tran;

                try
                {
                    int count = 0;

                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n];

                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;

                            count += cmd.ExecuteNonQuery();
                        }
                    }

                    tran.Commit();

                    return count;
                }
                catch
                {
                    tran.Rollback();

                    return -1;
                }
            }
        }

        /// <summary>
        /// 复制sqlparameters
        /// </summary>
        /// <param name="paramList"></param>
        /// <returns></returns>
        public static MySqlParameter[] DeepCloneMySqlParameters(MySqlParameter[] paramList)
        {
            if (paramList == null || paramList.Length <= 0)
            {
                return new MySqlParameter[] { };
            }

            List<MySqlParameter> temp = new List<MySqlParameter>();

            foreach (MySqlParameter param in paramList)
            {
                MySqlParameter p = new MySqlParameter();
                p.ParameterName = param.ParameterName;
                p.MySqlDbType = param.MySqlDbType;
                p.Value = param.Value;

                temp.Add(p);
            }

            return temp.ToArray();
        }

        /// <summary>
        /// 生成sqlparam
        /// </summary>
        /// <param name="name"></param>
        /// <param name="value"></param>
        /// <param name="t"></param>
        /// <returns></returns>
        public static MySqlParameter InitSqlParam(string name, object value, MySqlDbType t)
        {
            MySqlParameter param = new MySqlParameter();
            param.ParameterName = name;
            param.MySqlDbType = t;
            param.Value = value;
            return param;
        }
    }
}
